﻿Imports System.Runtime.Serialization
Imports System.Data.Common
Imports System.Data.SqlClient
Imports System.Text
Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Common

Namespace SqlServer
    ''' <summary>
    ''' SQL Server implementation of an Execution Provider.
    ''' </summary>
    ''' <remarks></remarks>
    Public Class SqlExecutionProvider
        Implements IExecutionProvider

        ''' <summary>
        ''' The connection.  The connection will be opened at creation and closed at disposal.
        ''' </summary>
        ''' <remarks></remarks>
        Private _connection As SqlConnection

        ''' <summary>
        ''' This gets created every time a script item is executed.
        ''' </summary>
        ''' <remarks></remarks>
        Private _output As StringBuilder

        ''' <summary>
        ''' This is the server object.  It has the same instancing timeline as _connection.
        ''' </summary>
        ''' <remarks></remarks>
        Private _server As Server

#Region " IExecutionContext"

        Public Sub Initialize(ByVal project As ScriptProject, ByVal connectionString As String) Implements IExecutionProvider.Initialize

            'Create the database connection
            _connection = New SqlConnection(connectionString)

            'Open the transaction
            _connection.Open()

            'Create the server
            _server = New Server(New ServerConnection(_connection))

            'Hook up the handlers
            AddHandler _server.ConnectionContext.InfoMessage, AddressOf InfoMessageHandler
            
        End Sub

        Public Function Execute(ByVal scriptItem As ScriptItem) As ScriptExecutionResult Implements IExecutionProvider.Execute



            If scriptItem Is Nothing Then Throw New ArgumentNullException("scriptItem")

            'We will store the output here
            _output = New StringBuilder()

            'Let's assume failure
            Dim success As Boolean = False

            'Now, we'll try to execute this thing
            Try

                'Execute the command
                _server.ConnectionContext.ExecuteNonQuery(scriptItem.CommandText, ExecutionTypes.Default)

                success = True

            Catch ex As SqlException

                'TODO: Add some more of the sql information
                _output.AppendLine(String.Format("Exception: {0}", ex.Message))

            Catch ex As ExecutionFailureException


                If ex.InnerException Is Nothing Then
                    _output.AppendLine(String.Format("ExecutionFailureException: {0}", ex.Message))
                Else
                    _output.AppendLine(ex.InnerException.Message)
                End If

            Catch ex As Exception

                _output.AppendLine(String.Format("Exception: {0}", ex.Message))

            End Try

            'Return the output
            Return New ScriptExecutionResult(scriptItem, _output.ToString(), success)

        End Function

#End Region

#Region " Event handlers"

        Private Sub InfoMessageHandler(ByVal sender As Object, ByVal e As SqlInfoMessageEventArgs)

            'Append the line 
            _output.AppendLine(e.Message)

        End Sub

#End Region

#Region " IDisposable Support "
        ''' <summary>
        ''' To detect redundant calls
        ''' </summary>
        ''' <remarks></remarks>
        Private _isDisposed As Boolean = False

        Public Sub Dispose() Implements IDisposable.Dispose

            'Check to see if we've already done this
            If Not _isDisposed Then

                'Clean up the event handlers
                If _server IsNot Nothing Then

                    'Clean up the event handlers
                    RemoveHandler _server.ConnectionContext.InfoMessage, AddressOf InfoMessageHandler

                    'Get rid of this
                    _server = Nothing

                End If

                'Check to see if the connection is nothing
                If _connection IsNot Nothing Then

                    'Dipose of it
                    _connection.Close()

                    'Ditch the reference to it
                    _connection = Nothing
                End If

                'No need to do this
                GC.SuppressFinalize(Me)

            End If
        End Sub

        Protected Overrides Sub Finalize()
            Me.Dispose()
        End Sub
#End Region

    End Class

End Namespace
